package com.yangjun.dao.impl.waresAnalyse;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.yangjun.entity.waresAnalyse.DayOutRateEntity;
import com.yangjun.entity.waresAnalyse.GoodsTableEntity;
import com.yangjun.entity.waresAnalyse.MonthOutQtyEntity;
import com.yangjun.entity.waresAnalyse.MonthOutRateEntity;
import com.yangjun.entity.waresAnalyse.WareSummaryEntity;


@Repository
public class WaresAnalyseDaoImpl {

	
	
	@Autowired
	@Resource(name = "datavJdbcTemplate")
	private JdbcTemplate jdbc;
	
	
	/**
	 * 添加用户搜索结果
	 * @param cookieid
	 * @param list
	 * @return
	 */
	public void insertCookieidWarecode(final String cookieid, final List<WareSummaryEntity> list) {
		
		
		System.out.println(cookieid+"\t insertCookieidWarecode 选择的warecode长度为=="+list.size());
		String sql = "insert into TMPTB_DV_SEARCH_WARECODE (cookieid,warecode) values (?,?)";
		jdbc.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
//				System.out.println("cookieid==="+cookieid);
//				System.out.println("list.get(i).getWarecode()==="+list.get(i).getWarecode());
				
				ps.setString(1, cookieid);
				ps.setString(2, list.get(i).getWarecode());
			}

			@Override
			public int getBatchSize() {
				return list.size();
			}
		});

	}
//	public void insertCookieidWarecodeV1(final String cookieid, final List<GoodsTableEntity> list) {
//		String sql = "insert into TMPTB_DV_SEARCH_WARECODE (cookieid,warecode) values (?,?)";
//		jdbc.batchUpdate(sql, new BatchPreparedStatementSetter() {
//			
//			@Override
//			public void setValues(PreparedStatement ps, int i) throws SQLException {
////				System.out.println("cookieid==="+cookieid);
////				System.out.println("list.get(i).getWarecode()==="+list.get(i).getWarecode());
//				
//				ps.setString(1, cookieid);
//				ps.setString(2, list.get(i).getWarecode());
//			}
//			
//			@Override
//			public int getBatchSize() {
//				return list.size();
//			}
//		});
//		
//	}
	
	
	public void insertCookieidWarecodeSelect(final String cookieid, final List<String> warecodeList) {
		
		System.out.println(cookieid+"\t insertCookieidWarecodeSelect 选择的warecode长度为=="+warecodeList.size());
		
		String sql = "insert into TMPTB_DV_SEARCH_WARECODE (cookieid,warecode) values (?,?)";
		jdbc.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
//				System.out.println("第"+i+"次插入数据:"+warecodeList.get(i));
				ps.setString(1, cookieid);
				ps.setString(2, warecodeList.get(i));
			}
			
			@Override
			public int getBatchSize() {
				return warecodeList.size();
			}
		});
		
	}
	
	
	
	/**
	 * 删除用户搜索结果
	 * @param cookieid
	 * @return
	 */
	public int deleteCookieidWarecode(String cookieid){
		String sql = "delete from TMPTB_DV_SEARCH_WARECODE where cookieid='"+cookieid+"'";
		return jdbc.update(sql);
	}
	/**
	 * 月度趋势
	 * @param warecode
	 * @param ordersource
	 * @param kpi
	 * @param range
	 * @return
	 */
	public List<Map<String,Object>> monthDirection(String warecode,
			String ordersource,String kpi,String range){
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("select t.data_mth,"
		         +"nvl(round(sum(decode(t.f_date, 01, "+kpi+")), 0), 0) as day01,"
		         +"nvl(round(sum(decode(t.f_date, 02, "+kpi+")), 0), 0) as day02,"
		         +"nvl(round(sum(decode(t.f_date, 03, "+kpi+")), 0), 0) as day03,"
		         +"nvl(round(sum(decode(t.f_date, 04, "+kpi+")), 0), 0) as day04,"
		         +"nvl(round(sum(decode(t.f_date, 05, "+kpi+")), 0), 0) as day05,"
		         +"nvl(round(sum(decode(t.f_date, 06, "+kpi+")), 0), 0) as day06,"
		         +"nvl(round(sum(decode(t.f_date, 07, "+kpi+")), 0), 0) as day07,"
		         +"nvl(round(sum(decode(t.f_date, 08, "+kpi+")), 0), 0) as day08,"
		         +"nvl(round(sum(decode(t.f_date, 09, "+kpi+")), 0), 0) as day09,"
		         +"nvl(round(sum(decode(t.f_date, 10, "+kpi+")), 0), 0) as day10,"
		         +"nvl(round(sum(decode(t.f_date, 11, "+kpi+")), 0), 0) as day11,"
		         +"nvl(round(sum(decode(t.f_date, 12, "+kpi+")), 0), 0) as day12,"
		         +"nvl(round(sum(decode(t.f_date, 13, "+kpi+")), 0), 0) as day13,"
		         +"nvl(round(sum(decode(t.f_date, 14, "+kpi+")), 0), 0) as day14,"
		         +"nvl(round(sum(decode(t.f_date, 15, "+kpi+")), 0), 0) as day15,"
		         +"nvl(round(sum(decode(t.f_date, 16, "+kpi+")), 0), 0) as day16,"
		         +"nvl(round(sum(decode(t.f_date, 17, "+kpi+")), 0), 0) as day17,"
		         +"nvl(round(sum(decode(t.f_date, 18, "+kpi+")), 0), 0) as day18,"
		         +"nvl(round(sum(decode(t.f_date, 19, "+kpi+")), 0), 0) as day19,"
		         +"nvl(round(sum(decode(t.f_date, 20, "+kpi+")), 0), 0) as day20,"
		         +"nvl(round(sum(decode(t.f_date, 21, "+kpi+")), 0), 0) as day21,"
		         +"nvl(round(sum(decode(t.f_date, 22, "+kpi+")), 0), 0) as day22,"
		         +"nvl(round(sum(decode(t.f_date, 23, "+kpi+")), 0), 0) as day23,"
		         +"nvl(round(sum(decode(t.f_date, 24, "+kpi+")), 0), 0) as day24,"
		         +"nvl(round(sum(decode(t.f_date, 25, "+kpi+")), 0), 0) as day25,"
		         +"nvl(round(sum(decode(t.f_date, 26, "+kpi+")), 0), 0) as day26,"
		         +"nvl(round(sum(decode(t.f_date, 27, "+kpi+")), 0), 0) as day27,"
		         +"nvl(round(sum(decode(t.f_date, 28, "+kpi+")), 0), 0) as day28,"
		         +"nvl(round(sum(decode(t.f_date, 29, "+kpi+")), 0), 0) as day29,"
		         +"nvl(round(sum(decode(t.f_date, 30, "+kpi+")), 0), 0) as day30,"
		         +"nvl(round(sum(decode(t.f_date, 31, "+kpi+")), 0), 0) as day31"
				 +" from bikad.vis_ware_analy_d t where t.data_mth >='"+range+"' ");
		if(StringUtils.isNotBlank(warecode)){
			sql.append(" and t.warecode in ( "+warecode+" ) ");
		}
		if(StringUtils.isNotBlank(ordersource)){
			sql.append(" and t.ordersource in ("+ordersource+") ");
		}
		sql.append(" group by t.data_mth order by t.data_mth");
		System.err.println("monthDirection sql =="+sql.toString());
		return jdbc.queryForList(sql.toString(), params.toArray());
		
	}
	public List<Map<String,Object>> rateMonthDirection(String warecode,
			String ordersource,String kpi,String divnum,String range){
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(
				"select data_mth, "
						+"      util_division("+kpi+"01, "+divnum+"01, 2) as day01, "
						+"      util_division("+kpi+"02, "+divnum+"02, 2) as day02, "
						+"      util_division("+kpi+"03, "+divnum+"03, 2) as day03, "
						+"      util_division("+kpi+"04, "+divnum+"04, 2) as day04, "
						+"      util_division("+kpi+"05, "+divnum+"05, 2) as day05, "
						+"      util_division("+kpi+"06, "+divnum+"06, 2) as day06, "
						+"      util_division("+kpi+"07, "+divnum+"07, 2) as day07, "
						+"      util_division("+kpi+"08, "+divnum+"08, 2) as day08, "
						+"      util_division("+kpi+"09, "+divnum+"09, 2) as day09, "
						+"      util_division("+kpi+"10, "+divnum+"10, 2) as day10, "
						+"      util_division("+kpi+"11, "+divnum+"11, 2) as day11, "
						+"      util_division("+kpi+"12, "+divnum+"12, 2) as day12,   "
						+"      util_division("+kpi+"13, "+divnum+"13, 2) as day13, "
						+"      util_division("+kpi+"14, "+divnum+"14, 2) as day14, "
						+"      util_division("+kpi+"15, "+divnum+"15, 2) as day15, "
						+"      util_division("+kpi+"16, "+divnum+"16, 2) as day16, "
						+"      util_division("+kpi+"17, "+divnum+"17, 2) as day17, "
						+"      util_division("+kpi+"18, "+divnum+"18, 2) as day18, "
						+"      util_division("+kpi+"19, "+divnum+"19, 2) as day19, "
						+"      util_division("+kpi+"20, "+divnum+"20, 2) as day20, "
						+"      util_division("+kpi+"21, "+divnum+"21, 2) as day21, "
						+"      util_division("+kpi+"22, "+divnum+"22, 2) as day22, "
						+"      util_division("+kpi+"23, "+divnum+"23, 2) as day23, "
						+"      util_division("+kpi+"24, "+divnum+"24, 2) as day24,  "
						+"      util_division("+kpi+"25, "+divnum+"25, 2) as day25, "
						+"      util_division("+kpi+"26, "+divnum+"26, 2) as day26, "
						+"      util_division("+kpi+"27, "+divnum+"27, 2) as day27, "
						+"      util_division("+kpi+"28, "+divnum+"28, 2) as day28, "
						+"      util_division("+kpi+"29, "+divnum+"29, 2) as day29, "
						+"      util_division("+kpi+"30, "+divnum+"30, 2) as day30, "
						+"      util_division("+kpi+"31, "+divnum+"31, 2) as day31 "
						+"   from (select data_mth, "
						+"              sum(decode(t.f_date, 01, "+kpi+")) as "+kpi+"01, "
						+"              sum(decode(t.f_date, 02, "+kpi+")) as "+kpi+"02, "
						+"              sum(decode(t.f_date, 03, "+kpi+")) as "+kpi+"03, "
						+"              sum(decode(t.f_date, 04, "+kpi+")) as "+kpi+"04, "
						+"              sum(decode(t.f_date, 05, "+kpi+")) as "+kpi+"05, "
						+"              sum(decode(t.f_date, 06, "+kpi+")) as "+kpi+"06, "
						+"              sum(decode(t.f_date, 07, "+kpi+")) as "+kpi+"07, "
						+"              sum(decode(t.f_date, 08, "+kpi+")) as "+kpi+"08, "
						+"              sum(decode(t.f_date, 09, "+kpi+")) as "+kpi+"09, "
						+"              sum(decode(t.f_date, 10, "+kpi+")) as "+kpi+"10, "
						+"              sum(decode(t.f_date, 11, "+kpi+")) as "+kpi+"11, "
						+"              sum(decode(t.f_date, 12, "+kpi+")) as "+kpi+"12, "
						+"              sum(decode(t.f_date, 13, "+kpi+")) as "+kpi+"13, "
						+"              sum(decode(t.f_date, 14, "+kpi+")) as "+kpi+"14, "
						+"              sum(decode(t.f_date, 15, "+kpi+")) as "+kpi+"15, "
						+"              sum(decode(t.f_date, 16, "+kpi+")) as "+kpi+"16, "
						+"              sum(decode(t.f_date, 17, "+kpi+")) as "+kpi+"17, "
						+"              sum(decode(t.f_date, 18, "+kpi+")) as "+kpi+"18, "
						+"              sum(decode(t.f_date, 19, "+kpi+")) as "+kpi+"19, "
						+"              sum(decode(t.f_date, 20, "+kpi+")) as "+kpi+"20, "
						+"              sum(decode(t.f_date, 21, "+kpi+")) as "+kpi+"21,    "
						+"              sum(decode(t.f_date, 22, "+kpi+")) as "+kpi+"22,   "
						+"              sum(decode(t.f_date, 23, "+kpi+")) as "+kpi+"23,    "
						+"              sum(decode(t.f_date, 24, "+kpi+")) as "+kpi+"24,    "
						+"              sum(decode(t.f_date, 25, "+kpi+")) as "+kpi+"25,   "
						+"              sum(decode(t.f_date, 26, "+kpi+")) as "+kpi+"26, "
						+"              sum(decode(t.f_date, 27, "+kpi+")) as "+kpi+"27,    "
						+"              sum(decode(t.f_date, 28, "+kpi+")) as "+kpi+"28,   "
						+"              sum(decode(t.f_date, 29, "+kpi+")) as "+kpi+"29, "
						+"              sum(decode(t.f_date, 30, "+kpi+")) as "+kpi+"30,    "
						+"              sum(decode(t.f_date, 31, "+kpi+")) as "+kpi+"31,   "
						+"              sum(decode(t.f_date, 01, "+divnum+")) as "+divnum+"01, "
						+"              sum(decode(t.f_date, 02, "+divnum+")) as "+divnum+"02, "
						+"              sum(decode(t.f_date, 03, "+divnum+")) as "+divnum+"03, "
						+"              sum(decode(t.f_date, 04, "+divnum+")) as "+divnum+"04, "
						+"              sum(decode(t.f_date, 05, "+divnum+")) as "+divnum+"05, "
						+"              sum(decode(t.f_date, 06, "+divnum+")) as "+divnum+"06, "
						+"              sum(decode(t.f_date, 07, "+divnum+")) as "+divnum+"07, "
						+"              sum(decode(t.f_date, 08, "+divnum+")) as "+divnum+"08, "
						+"              sum(decode(t.f_date, 09, "+divnum+")) as "+divnum+"09, "
						+"              sum(decode(t.f_date, 10, "+divnum+")) as "+divnum+"10, "
						+"              sum(decode(t.f_date, 11, "+divnum+")) as "+divnum+"11, "
						+"              sum(decode(t.f_date, 12, "+divnum+")) as "+divnum+"12, "
						+"              sum(decode(t.f_date, 13, "+divnum+")) as "+divnum+"13, "
						+"              sum(decode(t.f_date, 14, "+divnum+")) as "+divnum+"14, "
						+"              sum(decode(t.f_date, 15, "+divnum+")) as "+divnum+"15, "
						+"              sum(decode(t.f_date, 16, "+divnum+")) as "+divnum+"16, "
						+"              sum(decode(t.f_date, 17, "+divnum+")) as "+divnum+"17, "
						+"              sum(decode(t.f_date, 18, "+divnum+")) as "+divnum+"18, "
						+"              sum(decode(t.f_date, 19, "+divnum+")) as "+divnum+"19, "
						+"              sum(decode(t.f_date, 20, "+divnum+")) as "+divnum+"20, "
						+"              sum(decode(t.f_date, 21, "+divnum+")) as "+divnum+"21,    "
						+"              sum(decode(t.f_date, 22, "+divnum+")) as "+divnum+"22,   "
						+"              sum(decode(t.f_date, 23, "+divnum+")) as "+divnum+"23,    "
						+"              sum(decode(t.f_date, 24, "+divnum+")) as "+divnum+"24,    "
						+"              sum(decode(t.f_date, 25, "+divnum+")) as "+divnum+"25,   "
						+"              sum(decode(t.f_date, 26, "+divnum+")) as "+divnum+"26, "
						+"              sum(decode(t.f_date, 27, "+divnum+")) as "+divnum+"27,    "
						+"              sum(decode(t.f_date, 28, "+divnum+")) as "+divnum+"28,   "
						+"              sum(decode(t.f_date, 29, "+divnum+")) as "+divnum+"29, "
						+"              sum(decode(t.f_date, 30, "+divnum+")) as "+divnum+"30,    "
						+"              sum(decode(t.f_date, 31, "+divnum+")) as "+divnum+"31    "             
						+"         from BIKAD.vis_ware_analy_d t "
				+ "where t.data_mth >='"+range+"' ");
		if(StringUtils.isNotBlank(warecode)){
			sql.append(" and t.warecode in ( "+warecode+" )  ");
		}
		if(StringUtils.isNotBlank(ordersource)){
			sql.append(" and t.ordersource in ("+ordersource+") ");
		}
		sql.append(" group by t.data_mth ) B  order by data_mth");
		System.err.println("rateMonthDirection sql =="+sql.toString());
		return jdbc.queryForList(sql.toString(), params.toArray());
		
	}
	public List<Float> getDaykpiValue(DayOutRateEntity entity){
		List<Float> list = new ArrayList<Float>();
		list.add(entity.getDay01());
		list.add(entity.getDay02());
		list.add(entity.getDay03());
		list.add(entity.getDay04());
		list.add(entity.getDay05());
		list.add(entity.getDay06());
		list.add(entity.getDay07());
		list.add(entity.getDay08());
		list.add(entity.getDay09());
		list.add(entity.getDay10());
		list.add(entity.getDay11());
		list.add(entity.getDay12());
		list.add(entity.getDay13());
		list.add(entity.getDay14());
		list.add(entity.getDay15());
		list.add(entity.getDay16());
		list.add(entity.getDay17());
		list.add(entity.getDay18());
		list.add(entity.getDay19());
		list.add(entity.getDay21());
		list.add(entity.getDay22());
		list.add(entity.getDay23());
		list.add(entity.getDay24());
		list.add(entity.getDay25());
		list.add(entity.getDay26());
		list.add(entity.getDay27());
		list.add(entity.getDay28());
		list.add(entity.getDay29());
		list.add(entity.getDay30());
		list.add(entity.getDay31());
		return list;
	}

	
	public List<Float> getDaykpiValue(DayOutRateEntity entity,int limit){
	    List<Float> list = new ArrayList<Float>();
	    for(int i=1;i<=limit;i++){
	      if(i==1){
	        list.add(entity.getDay01());
	      }else if(i==2){
	        list.add(entity.getDay02());
	      }else if(i==3){
	        list.add(entity.getDay03());
	      }else if(i==4){
	        list.add(entity.getDay04());
	      }else if(i==5){
	        list.add(entity.getDay05());
	      }else if(i==6){
	        list.add(entity.getDay06());
	      }else if(i==7){
	        list.add(entity.getDay07());
	      }else if(i==8){
	        list.add(entity.getDay08());
	      }else if(i==9){
	        list.add(entity.getDay09());
	      }else if(i==10){
	        list.add(entity.getDay10());
	      }else if(i==11){
	        list.add(entity.getDay11());
	      }else if(i==12){
	        list.add(entity.getDay12());
	      }else if(i==13){
	        list.add(entity.getDay13());
	      }else if(i==14){
	        list.add(entity.getDay14());
	      }else if(i==15){
	        list.add(entity.getDay15());
	      }else if(i==16){
	        list.add(entity.getDay16());
	      }else if(i==17){
	        list.add(entity.getDay17());
	      }else if(i==18){
	        list.add(entity.getDay18());
	      }else if(i==19){
	        list.add(entity.getDay19());
	      }else if(i==20){
	        list.add(entity.getDay20());
	      }else if(i==21){
	        list.add(entity.getDay21());
	      }else if(i==22){
	        list.add(entity.getDay22());
	      }else if(i==23){
	        list.add(entity.getDay23());
	      }else if(i==24){
	        list.add(entity.getDay24());
	      }else if(i==25){
	        list.add(entity.getDay25());
	      }else if(i==26){
	        list.add(entity.getDay26());
	      }else if(i==27){
	        list.add(entity.getDay27());
	      }else if(i==28){
	        list.add(entity.getDay28());
	      }else if(i==29){
	        list.add(entity.getDay29());
	      }else if(i==30){
	        list.add(entity.getDay30());
	      }else if(i==31){
	        list.add(entity.getDay31());
	      }
	    }
	    return list;
	  }
	public List<Object> getMonthkpiValue(MonthOutRateEntity entity,int limit){
		List<Object> list = new ArrayList<Object>();
		for(int i=1;i<=limit;i++){
			if(i==1){
				list.add(entity.getMth01());
			}else if(i==2){
				list.add(entity.getMth02());
			}else if(i==3){
				list.add(entity.getMth03());
			}else if(i==4){
				list.add(entity.getMth04());
			}else if(i==5){
				list.add(entity.getMth05());
			}else if(i==6){
				list.add(entity.getMth06());
			}else if(i==7){
				list.add(entity.getMth07());
			}else if(i==8){
				list.add(entity.getMth08());
			}else if(i==9){
				list.add(entity.getMth09());
			}else if(i==10){
				list.add(entity.getMth10());
			}else if(i==11){
				list.add(entity.getMth11());
			}else if(i==12){
				list.add(entity.getMth12());
			}
		}
		return list;
	}
	public List<Object> getMonthkpiValue(MonthOutRateEntity entity){
		List<Object> list = new ArrayList<Object>();
		list.add(entity.getMth01());
		list.add(entity.getMth02());
		list.add(entity.getMth03());
		list.add(entity.getMth04());
		list.add(entity.getMth05());
		list.add(entity.getMth06());
		list.add(entity.getMth07());
		list.add(entity.getMth08());
		list.add(entity.getMth09());
		list.add(entity.getMth10());
		list.add(entity.getMth11());
		list.add(entity.getMth12());
		return list;
		
	}
}
